"""
该脚本作用对象为
升级过后 排期软件自身的数据库
排期软件数据库的数据读写操作类

"""
from .CONFIG import CGTW,SCHEDULER,CGTW_LOGIN
import datetime
import psycopg2
from .mz_date import MZ_Date
import requests
import json
from requests.packages.urllib3.exceptions import InsecureRequestWarning
requests.packages.urllib3.disable_warnings(InsecureRequestWarning)
"""

    -------------------------------------------------------------------------------
    数据库配置
    -------------------------------------------------------------------------------

"""
# DATABASE = 'forclear'
# USER = 'postgres'
# PASSWORD = 'timeaxis'
# HOST = '192.168.1.214'
# PORT= 5432
DATABASE = SCHEDULER['database']
USER = SCHEDULER['user']
PASSWORD = SCHEDULER['password']
HOST = SCHEDULER['host']
PORT= SCHEDULER['port']



class SchedulerHandler(object):

    def __init__(self,_CGTW=None,_SCHEDULER=None):
        super(SchedulerHandler, self).__init__()

        self.real_sch_host = _SCHEDULER['host'] if _SCHEDULER else HOST
        self.real_sch_port = _SCHEDULER['port'] if _SCHEDULER else PORT

        self.real_cgtw_host = _CGTW['host'] if _CGTW else CGTW['host']
        self.real_cgtw_port = _CGTW['port'] if _CGTW else CGTW['port']

        self.mz_date = MZ_Date()
        self.r_project_info = self.__r_project_info()
        self.projects_info = self.__projects_info()

    def _init_timeaxis_db(self):
        """
        初始化222排期数据库timeaxis连接
        :return: time_connect, time_cursor
        """

        try:
            time_connect = psycopg2.connect(database= DATABASE,
                                            user=USER,
                                            password=PASSWORD,
                                            host=self.real_sch_host,
                                            port=self.real_sch_port)
            time_cursor = time_connect.cursor()
        except Exception as cgdb_err:
            print('cgdb_err:', cgdb_err)
            return None, None
        return time_connect, time_cursor


    def _init_taskprogress_db(self):
        """
        初始化222排期数据库taskprogress连接
        :return:
        """
        try:
            time_connect = psycopg2.connect(database='taskprogress',
                                          user='timeaxis',
                                          password='timeaxis',
                                          host=self.real_sch_host,
                                          port=self.real_sch_port)
            time_cursor = time_connect.cursor()
        except Exception as _init_taskprogress_db_ERR:
            print('_init_taskprogress_db_ERR:',str(_init_taskprogress_db_ERR))
            return None, None
        return time_connect, time_cursor



    """
    ***********************************************
        public_conf表
        public_conf表记录排期软件中项目公共配置
        字段:
        id:
        week_info: 本周优先任务栏信息 (已弃用)
        project_info:  项目列表栏信息(已弃用)
        halfyear_info: 半年报项目任务信息(已弃用)
        public_info: 公告配置信息
        token: 任务的token
        pipeline: 各部门阶段名称集合


        所需信息需根据具体要求来
    ***********************************************
    """
    def get_public_conf_info(self):
        """
        获取public_conf表中公告配置
        其中public_info为所有的自定义信息，包括自定义放假日期
        token：用于获取缩略图所用
        pipeline：为各部门所含的所有可能存在阶段名称
        :return:
        """
        public_conf, token, pipeline =None,None,None
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select public_info,token,pipeline from timeaxis.public_conf"
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchone()
            if result:
                public_conf,token,pipeline = result
        except Exception as get_public_conf_info_ERR:
            print('get_public_conf_info_ERR:',str(get_public_conf_info_ERR))
        finally:
            time_connect.close()
        return public_conf,token,pipeline


    def get_token(self,login_data=None):
        """
        通过请求获取获取token
        :param login_data: 用于登录cgtw的账号信息，参考cgtw_login
        :return:
        """
        token = ''
        try:
            data = login_data if login_data else CGTW_LOGIN
            # url = f'https://{CGTW["host"]}:{CGTW["api_port"]}/api.php'
            url = f'https://{self.real_cgtw_host}:{CGTW["api_port"]}/api.php'
            sdata = {"data": json.dumps(data)}
            req = requests.post(url, data=sdata, verify=False)
            result = json.loads(req.text)
            back_data = result.get('data', {})
            token = back_data.get('token', '')
            print('token:',token)
        except Exception as get_token_ERR:
            print("get_token_ERR:",str(get_token_ERR))

        return token

    """
    ***********************************************
        r_project表操作
        r_project表同步cgtw的pulic/project表中信息
        字段:
        id: r_project表中id
        cg_id: cgtw数据库pulic中项目的id
        database: cgtw数据库中schemas名称 eg: proj_xxss
        entity_name: 项目拼音简称 eg : XXSS
        full_name:   项目中文名称 eg: 西夏死书
        color:   cgtw中项目的颜色
        statuts: 项目状态  eg: Active,Close
        eps:  项目中含有的集数 eg: {"eps_list":["01","02",...]}
        pipeline_list: 该项目的所有阶段 eg: {"pipeline_list":["mod_c","rig","efx","layout","cpt_c",...]}
        frame_rate:   该项目的帧数率  需要cgtw上设定

    ***********************************************
    """


    def __r_project_info(self):
        """
        获取r_project 所有项目信息
        :return: 返回以项目schemas为键，{data}为值的字典

        整体返回结果：{database:{id:xx,cg_id:xx,database:xx,entity_name:xx,full_name:xx,project_name:xx,
        color:xx,status:xx,resolution:xx,frame_rate:xx},
        ...}
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,cg_id,database,entity_name,full_name," \
            "color,status,resolution,frame_rate,path," \
            "eps " \
            " from timeaxis.r_project"
        info_map={}  #{database:data,...}
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                # print(result)
                for i in result:
                    pid,cg_id,database,entity_name,full_name,\
                    color,status,resolution,frame_rate,path,\
                    eps = i
                    data={}
                    data['id'] = pid
                    data['cg_id'] = cg_id
                    data['database'] = database
                    data['entity_name'] = entity_name
                    data['full_name'] = full_name
                    data['project_name'] = f'{full_name}({entity_name})'
                    data['color'] = color
                    data['status'] = status
                    data['resolution'] = resolution
                    data['frame_rate'] = frame_rate
                    data['path'] = path
                    data['eps'] = eps  # eps01|eps02|...
                    info_map[database] = data
        except Exception as project_info_ERR:
            print('project_info_ERR:',str(project_info_ERR))
        finally:
            time_connect.close()

        return info_map


    def get_project_map(self):
        """
        获取当前r_project表中项目信息
        :return: 返回以项目schemas名称为键，以[中文，简称]为值的字典，默认非制作项目都算作：'noproject':['非制作项目','NON']
        eg:{database:["无心法师","WXFS"],...}
        """
        db_projectname_map={'noproject':['非制作项目','NON']}  #{database:["无心法师","WXFS"],...}
        if self.r_project_info:
            for database,data in self.r_project_info.items():
                db_projectname_map[database]=[data['full_name'],data['entity_name']]

        return db_projectname_map



    def get_project_color_map(self,projects_info=None):
        """
        根据激活的项目，获取项目对应的颜色信息，用于wait状态的任务条.
        这个只需要初始化一次就行了，比较新项目创建不会很频繁
        :param:projects_info: 传入指定的project_info
        :return:{database:{id:xx,cg_id:xx,database:xx,entity_name:xx,full_name:xx,color:xx,status:xx,eps:xx,pipeline_list:xx,frame_rate},
        ...}
        :return:
        """
        projects_info = projects_info if projects_info else self.projects_info
        _project_color_map ={}
        if projects_info:
            for database, data in projects_info.items():
                _project_color_map[database] = data['color']
        return _project_color_map


    def _database_project_info_map(self,projects_info):
        """
        以database为键的，projects表里关联的项目信息，也就是要显示出来的项目信息
        :param:projects_info: 传入指定的project_info
        :return:
        database_project_info_map:{'proj_msyy':{data},'proj_yckt':{data},...}
        projectname_database_info_map : {'西夏死书(XXSS)':'proj_xxss',...}
        """
        database_project_info_map={}
        projects_info = projects_info if projects_info else self.projects_info
        if projects_info:
            for v in projects_info.values():
                database=v['database']
                database_project_info_map[database]=v
        return database_project_info_map


    def get_project_eps_list_map(self):
        """
        获取项目schema与其对应集数列表的信息
        :return: {'proj_xxss':['01','02',..],''}
        """
        project_eps_list_map = {}
        if self.r_project_info:
            for database, data in self.r_project_info.items():
                eps = data.get('eps','')
                eps_list = eps.split('|') if eps else []
                project_eps_list_map[database] = eps_list
        return project_eps_list_map



    """
    ***********************************************
        account表
        account表操作排期软件人员账号、名称、密码、部门以及排期软件UI配置记录
        字段:
        id: 
        account: 账号
        user_level: 权限等级
        custom_json: UI配置
        password: 密码
        user_name: 名称
        department: 部门
        
    ***********************************************
    """

    def get_account_info(self):
        """
        获取排期软件账户信息
        :return: 返回一个列表，每个元素是每人的信息，[data,data,...]
                其中data: {id:xx,account:xxx,user_level:xx,custom_json:xx,password:xx,user_name:xxx,department:xxx}
                        其中：
                           custom_json为用户当前界面配置，包括：
                                       {
                                            "date": "2020-12-28",
                                            "database": [
                                                "proj_jzsg",
                                                "proj_msyy"
                                            ],
                                            "departments": [
                                                "07特效二"
                                            ],
                                            "graphic_setting": [
                                                370,
                                                150,
                                                20,
                                                142,
                                                "",
                                                12
                                            ]
                                        }
                            account ：为登录拼音名称
                            user_name :为显示的中文名称
                            account_info : 为额外用户配置，留个坑，暂时没用

        """

        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select id,account,user_level,custom_json,password,user_name,account_info from timeaxis.account"
        info_list=[]  #[data,data,...]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    pid,account,user_level,custom_json,password,user_name,account_info=i
                    data={}
                    data['id']=pid
                    data['account']=account
                    data['user_level']=user_level
                    data['custom_json']=custom_json
                    data['password']=password
                    data['user_name']=user_name
                    data['account_info']=account_info

                    info_list.append(data)
        except Exception as account_info_ERR:
            print('account_info_ERR:',str(account_info_ERR))
        finally:
            time_connect.close()

        return info_list


    def get_current_account_info(self,account,password):
        """
        获取当前输入账户密码账户信息
        :param :accout : 账号
        :param :password : 密码

        :return: 返回{'account':xx,'user_level':xx,'user_name':xx,'account_info':xx,'department':xx,'database':xx,
                      'startday':xxx, 'graphic_setting':xx }

        """

        time_connect, time_cursor = self._init_timeaxis_db()
        sql="select account,user_level,custom_json,user_name,account_info " \
            "from timeaxis.account where account='{}' and password='{}'".format(account, password)
        info = {}
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchone()
            if result:
                today = datetime.datetime.strftime(datetime.datetime.today(), "%Y-%m-%d")
                account,user_level,custom_json,user_name,account_info=result
                info['account']=account
                info['user_level']=user_level
                info['user_name']=user_name
                info['account_info']=account_info
                info['departments']=custom_json.get('departments', [])
                info['database']=custom_json.get('database', [])
                info['date']=custom_json.get('date', today)
                info['graphic_setting'] = custom_json.get('graphic_setting', [])

        except Exception as account_info_ERR:
            print('account_info_ERR:',str(account_info_ERR))
        finally:
            time_connect.close()

        return info


    def save_custom_conf(self,account,data):
        """
        保存当前用户配置信息，密码及其他等级以及名称修改用其他更高权限的函数
        :param data:
        :return:
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "update timeaxis.account set custom_json='{}' where account = '{}' ".format(data, account)
        try:
            time_cursor.execute(sql)
            time_connect.commit()
        except Exception as save_custom_conf_ERR:
            print('save_custom_conf_ERR:',str(save_custom_conf_ERR))
        finally:
            time_connect.close()


    """
    ***********************************************
        r_account表
        r_account表同步cgtw中的public/account表信息，获取人员的部门、等级、名称
        字段:
        id: r_account 表id
        cg_id:    cgtw中account中的id
        department_id:   cgtw中public schema中config表里列名为object的值为department对应的表中id，可以通过该字段去r_department中查询对应的部门
        level:  人员等级
        artist: 人员名称
        active_department: 激活部门，即当前人员所在部门
    ***********************************************
    """

    def get_r_account_info(self):
        """
        获取r_account表信息

        :return: 返回一个以department_id为键，各人员信息[{id,cg_id,department_id,level,artist,active_department},..]列表为值的字典
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select id,cg_id,department_id,level,artist,active_department from timeaxis.r_account"
        info_map = {}  # [data,data,...]
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    pid, cg_id, department_id, level, artist,active_department = i
                    data = {}
                    data['id'] = pid
                    data['cg_id'] = cg_id
                    data['department_id'] = department_id
                    data['level'] = level
                    data['artist'] = artist
                    data['active_department'] = active_department
                    if department_id not in info_map.keys():
                        info_map[department_id] = [data]
                    else:
                        info_map[department_id].append(data)

        except Exception as r_account_info_ERR:
            print('r_account_info_ERR:',str(r_account_info_ERR))
        finally:
            time_connect.close()

        return info_map

    def get_account_id_artist_map(self,r_account_info=None):
        account_id_artist_map = {}
        if not r_account_info:
            r_account_info = self.get_r_account_info()

        for v in r_account_info.values():
            for data in v:
                account_id_artist_map[data['cg_id']] = data['artist']

        return account_id_artist_map

    def get_artist_level_map(self,r_account_info=None):
        artist_level_map = {}
        if not r_account_info:
            r_account_info = self.get_r_account_info()

        for v in r_account_info.values():
            for data in v:
                artist_level_map[data['artist']] = data['level']

        return artist_level_map

    def get_account_id_level_map(self,r_account_info=None):
        """
        使用account_id来表示人，而不再是人名，因为人名有重复
        :param r_account_info:
        :return:
        """
        account_id_level_map = {}
        if not r_account_info:
            r_account_info = self.get_r_account_info()

        for v in r_account_info.values():
            for data in v:
                account_id_level_map[data['cg_id']] = data['level']
        return account_id_level_map

    def r_account_artist_map_in_display_departments(self):
        """
        查询显示部门的所有人员信息,这些部门的人员
        :return:
        department_id_name_map:{'部门id':'部门名称',...}
        account_id_artist_map:{'人员id':'人员名称',...}
        account_id_department_map:{'人员id':'部门名称',...}
        """
        department_id_name_map = {} #{department_id:xxx,...}
        account_id_artist_map = {}  #{account_id:'张三',account_id:'xxx'}
        account_id_department_map = {}
        department_id_sql = "select cg_id,name from timeaxis.r_department where display = 1"
        time_connect, time_cursor = self._init_timeaxis_db()

        try:
            time_cursor.execute(department_id_sql)
            result=time_cursor.fetchall()
            if result:

                for i in result:
                    department_id,department = i
                    department_id_name_map[department_id] = department

                connect_did = '|'.join(department_id_name_map.keys())
                account_sql = "select cg_id,artist,department_id from timeaxis.r_account where department_id similar to '{}'".format(connect_did)
                time_cursor.execute(account_sql)
                a_result = time_cursor.fetchall()
                if a_result:
                    for a in a_result:
                        account_id,artist,department_id = a
                        account_id_artist_map[account_id] = artist
                        account_id_department_map[account_id] = department_id_name_map.get(department_id,'')

        except Exception as r_account_artist_map_in_display_departments_ERR:
            print('r_account_artist_map_in_display_departments_ERR:',str(r_account_artist_map_in_display_departments_ERR))
        finally:
            time_connect.close()

        return department_id_name_map,account_id_artist_map,account_id_department_map


    """
    ***********************************************
        r_department表
        r_department表同步cgtw中的public/conf_department表信息，获取部门信息
        字段:
        id:
        cg_id: cgtw表public/conf_department中的id
        name:  部门名称
        display: 是否在排期软件显示该部门
        
    ***********************************************
    """

    def r_department_info(self):
        """
        获取r_department表信息

        :return: 返回
        1.以部门名称为键,data为值的所有部门字典  {'01美术':{id,cg_id,name,display}}
        2.显示部门的信息
        3.显示部门的名称列表
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select id,cg_id,name,display from timeaxis.r_department"
        info_map = {}  # {'01美术':data,'02跟踪':data,...}
        display_department_map = {}  # 仅显示的部门的信息
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    pid, cg_id, name, display = i
                    data = {}
                    data['id'] = pid
                    data['cg_id'] = cg_id
                    data['name'] = name
                    data['display'] = display
                    info_map[name] = data
                    if display == 1:
                        display_department_map[name] = data
        except Exception as r_department_info_ERR:
            print('r_department_info_ERR:', str(r_department_info_ERR))
        finally:
            time_connect.close()

        return info_map,display_department_map,list(display_department_map.keys())

    """
    ***********************************************
        r_task表
        r_task表同步cgtw中的各激活项目对应schema的entity表信息，获取部门信息
        字段:
        id:
        cg_id: cgtw表task表中的id
        description: 任务描述
        name: 资产或者镜头名称
        eps: 所在集数
        img_path:  任务缩略图路径
        frame : 帧数
        assign_pipeline: 相关阶段
        shot_difficulty_level : 镜头难度，资产没有
        deadline: 任务最终提交日期
        artist:  制作者
        module:  任务模块 shot/asset
        task_name: 任务阶段名称
        status:  任务状态
        client_status_retake_count:  客户返修次数
        assigned: 是否已分配,当为0时会出现在排期软件未分配栏中
        database: 对应项目schema名称 eg :proj_xxss
        difficulty_level: 任务难度等级
        important_task_mark:  任务难易度
        workhour: 工时
        last_workhour: 最后分配工时
        department: 任务制作者所属部门
        retake_workhour: 返修工时               
        last_submit_time: 最后提交时间
        task_priority: 任务优先级
        

        r_task信息量太大,根据需要设置提取函数
    ***********************************************
    """

    def r_task_info(self):
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select id,cg_id,description,name,eps," \
              "img_path,frame,assign_pipeline,shot_difficulty_level,deadline," \
              "artist,module,task_name,status,client_status_retake_count," \
              "assigned,database,difficulty_level,important_task_mark,workhour," \
              "last_workhour,department,retake_workhour,last_submit_time,task_priority " \
              "from timeaxis.r_task"
        data_list=[]  #
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    data = {}
                    data['id'],data['cg_id'],data['description'],data['name'],data['eps'],\
                    data['img_path'],data['frame'],data['assign_pipeline'],data['shot_difficulty_level'],data['deadline'],\
                    data['artist'],data['module'],data['task_name'],data['status'],data['client_status_retake_count'],\
                    data['assigned'],data['database'],data['difficulty_level'],data['important_task_mark'],data['workhour'],\
                    data['last_workhour'],data['department'],data['retake_workhour'],data['last_submit_time'],data['task_priority']=i
                    data_list.append(data)
        except Exception as r_task_info_ERR:
            print('r_task_info_ERR:',str(r_task_info_ERR))
        finally:
            time_connect.close()

        return data_list


    def filter_r_task_info(self,databases,departments):
        """
        获取未分配的指定database与department字段的内容
        :param databases: ['proj_by','proj_xxss',...]
        :param departments: ['01美术','01跟踪',...]
        :return:
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        connect_database = '|'.join(databases)
        connect_department = '|'.join(departments)

        sql = "select id,cg_id,description,name,eps," \
              "img_path,frame,assign_pipeline,shot_difficulty_level,deadline," \
              "artist,module,task_name,status,client_status_retake_count," \
              "assigned,database,difficulty_level,important_task_mark,workhour," \
              "last_workhour,department,retake_workhour,last_submit_time,task_priority," \
              "task_pipeline,pinyin,account_id " \
              f"from timeaxis.r_task where database similar to '({connect_database})' and department similar to '({connect_department})' " \
              "and status not in('通用素材','Close') and assigned = 0"
        data_list = []  #
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                # print('filter_r_task_info :',len(result))
                for i in result:
                    data = {}
                    data['id'],data['cg_id'],data['description'],data['name'],data['eps'],\
                    data['img_path'],data['frame'],data['assign_pipeline'],data['shot_difficulty_level'],data['deadline'],\
                    data['artist'],data['module'],data['task_name'],data['status'],data['client_status_retake_count'],\
                    data['assigned'],data['database'],data['difficulty_level'],data['important_task_mark'],data['workhour'],\
                    data['last_workhour'],data['department'],data['retake_workhour'],data['last_submit_time'],data['task_priority'],\
                    data['task_pipeline'],data['pinyin'],data['account_id'] = i
                    data['show_name'] = '-'.join([data['name'],data['task_name']])  #计算show_name
                    if data['artist']:
                        data_list.append(data)
        except Exception as r_task_info_ERR:
            print('r_task_info_ERR:',str(r_task_info_ERR))
        finally:
            time_connect.close()

        return data_list


    def getDescriptionByDatabase(self,database):
        """
        获取任务描述信息
        :return:
        """
        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select description,frame,assign_pipeline,shot_difficulty_level,module," \
              "task_name,task_pipeline,database,difficulty_level,workhour " \
              f"from timeaxis.r_task where database = '{database}'"
        data_list = []  #
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    description, frame, assign_pipeline, shot_difficulty_level, module,\
                    task_name,task_pipeline,database,difficulty_level,workhour = i
                    data_list.append([description, frame, assign_pipeline, shot_difficulty_level, module,
                                      task_name,task_pipeline,database,difficulty_level,workhour])

        except Exception as getDescriptionByDatabaseERR:
            print('getDescriptionByDatabaseERR:',getDescriptionByDatabaseERR)

        finally:
            time_connect.close()

        return data_list


    """
    ***********************************************
        task表
        task表保存排期软件中所以分配的cgtw关联的任务信息
        字段:
        id:
        task_type: 任务类型
        r_id:  r_task中的id
        artist: 制作者
        position: 任务排期索引
        start_date: 起始安排日期
        end_date:   结束安排日期
        status:     任务状态
        cg_id: cgtw表task表中的id
        retake_info: 返修历史
        task_name:   cgtw任务名称
        task_pipeline:   任务阶段
        project_name:  项目名称 eg:冰糖炖雪梨(BT)
        artist_level:  制作者等级
        difficulty_level: 任务难度等级
        workhour:  工时
        module:    任务模块类型 asset/shot
        description: 任务描述
        assigned_time: 分配时间
        eps: 所在集数
        img_path: 缩略图路径
        important_task_mark:  任务难度标记
        show_name:  任务显示名称
        assigner:   分配者
        database:   任务所在schema名称 eg : proj_xxss
        note:    任务备注
        project_shortname:  任务简称 eg: XXSS
        show_workhour:  任务显示工时
        client_status_retake_count:  任务返修次数
        sort_right:  是否排序正确, 不正确的则会显示叹号图标
        assign_pipeline: 关联阶段
        department: 制作者所在部门
        project_color: 项目颜色
        retake_workhour: 返修工时
        date_length: 跨日期天数
        ignore_weekend: 是否忽略周日
        last_submit_time: 最后提交时期
        separted: 是否为拆分任务
        frame:  任务帧数  
        ingroup:  是否在任务组里
        cgworkhour: cgtw上的工时
        assigned_workhour: 已分配任务工时
        name: 镜头名称
        deadline: 镜头最终提交日期

        所需信息需根据具体要求来
    ***********************************************
    """


    def cgtwtask_in_departments_dates(self,departments,s_start_date, s_end_date):
        """
        根据传入的部门，起始结束日期来获取task表里的任务
        :param departments: 选择的部门
        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回为每个任务的数据列表

        """
        cgtw_tasks=[]
        time_connect, time_cursor = self._init_timeaxis_db()
        connect_department = '|'.join(departments)
        task_sql = "select id,task_type,r_id,artist,position," \
                   "start_date,end_date,status,cg_id,retake_info," \
                   "task_name,task_pipeline,project_name,artist_level,difficulty_level," \
                   "workhour,module,description,assigned_time,eps," \
                   "img_path,important_task_mark,show_name,assigner,database," \
                   "note,project_shortname,show_workhour,client_status_retake_count,sort_right," \
                   "assign_pipeline,department,project_color,retake_workhour,date_length," \
                   "ignore_weekend,last_submit_time,separated,frame,ingroup," \
                   "cgworkhour,assigned_times,assigned_workhour,name,deadline " \
                   "from timeaxis.task where department similar to '({})' and start_date <='{}' and end_date >='{}'".format(
            connect_department,s_end_date,s_start_date)
        try:
            time_cursor.execute(task_sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    data={}
                    data['id'], data['task_type'], data['r_id'], data['artist'], data['position'], \
                    data['start_date'],data['end_date'], data['status'], data['cg_id'], data['retake_info'], \
                    data['task_name'],data['task_pipeline'], data['project_name'], data['artist_level'], data['difficulty_level'], \
                    data['workhour'],data['module'], data['description'], data['assigned_time'], data['eps'],\
                    data['img_path'],data['important_task_mark'], data['show_name'], data['assigner'], data['database'],\
                    data['note'],data['project_shortname'], data['show_workhour'], data['client_status_retake_count'], data['sort_right'],\
                    data['assign_pipeline'],data['department'], data['project_color'], data['retake_workhour'], data['date_length'],\
                    data['ignore_weekend'],data['last_submit_time'], data['separated'], data['frame'], data['ingroup'],\
                    data['cgworkhour'],data['assigned_times'], data['assigned_workhour'], data['name'], data['deadline'] =i
                    cgtw_tasks.append(data)
        except Exception as cgtwtask_in_departments_dates_ERR:
            print('cgtwtask_in_departments_dates_ERR:',str(cgtwtask_in_departments_dates_ERR))
        finally:
            time_connect.close()

        return cgtw_tasks

    def customtask_in_departments_dates(self,departments,s_start_date, s_end_date):
        """
        根据传入的部门，起始结束日期来获取custom_task表里的任务
        :param departments: 选择的部门
        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回为每个任务的数据列表

        """
        custom_tasks=[]
        time_connect, time_cursor = self._init_timeaxis_db()
        connect_department = '|'.join(departments)
        task_sql = "select id,cg_id,start_date,end_date,artist," \
                   "workhour,description,task_type,custom_type,note," \
                   "position,show_workhour,parent_data,show_name,assigned_time," \
                   "department,project_name,color,date_length,ignore_weekend," \
                   "font_color,database,assigner,status " \
                   "from timeaxis.custom_task where department similar to '({})' and start_date <='{}' and end_date >='{}'".format(
            connect_department,s_end_date,s_start_date)
        try:
            time_cursor.execute(task_sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    data={}
                    data['id'], data['cg_id'], data['start_date'], data['end_date'], data['artist'], \
                    data['workhour'],data['description'], data['task_type'], data['custom_type'], data['note'],\
                    data['position'],data['show_workhour'], data['parent_data'], data['show_name'], data['assigned_time'],\
                    data['department'],data['project_name'], data['color'], data['date_length'], data['ignore_weekend'], \
                    data['font_color'],data['database'], data['assigner'], data['status'] = i
                    custom_tasks.append(data)
        except Exception as customtask_in_departments_dates_ERR:
            print('customtask_in_departments_dates_ERR:',str(customtask_in_departments_dates_ERR))
        finally:
            time_connect.close()

        return custom_tasks

    def grouptask_in_departments_dates(self,departments,s_start_date,s_end_date):
        """
        根据传入的部门，起始结束日期来获取group_task表里的任务
        :param departments: 选择的部门
        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回以人名为key的任务组信息
                 {artist1:{gid1:{'gdata':gdata,'task_data':task_data},gid2:{'gdata':gdata,'task_data':task_data}....},
                  artist1:{gid1:{'gdata':gdata,'task_data':task_data},gid2:{'gdata':gdata,'task_data':task_data}....},
                  ...
                  }
        """
        group_tasks={}
        time_connect, time_cursor = self._init_timeaxis_db()
        connect_department = '|'.join(departments)
        task_sql = "select id,group_name,color,project_name,department," \
                   "start_date,end_date,date_length,workhour,artist," \
                   "position,artist_level,note,assigned_time,task_id_dict," \
                   "tasks,use_custom_workhour,database,account_id  " \
                   "from timeaxis.group_task where department similar to '({})' and start_date <='{}' and end_date >='{}'".format(
            connect_department,s_end_date,s_start_date)
        try:
            time_cursor.execute(task_sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    gdata={}
                    gdata['id'], gdata['group_name'], gdata['color'], gdata['project_name'], gdata['department'], \
                    gdata['start_date'],gdata['end_date'], gdata['date_length'], gdata['workhour'], gdata['artist'],\
                    gdata['position'],gdata['artist_level'], gdata['note'], gdata['assigned_time'], gdata['task_id_dict'],\
                    gdata['tasks'],gdata['use_custom_workhour'], gdata['database'],gdata['account_id']= i


                    task_data = {}  # {cg_id1:data,cg_id2:data}
                    if gdata['task_id_dict']:  # 解析数据，这是还是全读取已存的所有任务
                        cg_ids = gdata['task_id_dict']['cg_ids']  # 默认类型就是list，不需要再转
                        cg_ids_connect = '|'.join(cg_ids)
                        task_sql = "select id,task_type,r_id,artist,position," \
                                   "start_date,end_date,status,cg_id,retake_info," \
                                   "task_name,task_pipeline,project_name,artist_level,difficulty_level," \
                                   "workhour,module,description,assigned_time,eps," \
                                   "img_path,important_task_mark,show_name,assigner,database," \
                                   "note,project_shortname,show_workhour,client_status_retake_count,sort_right," \
                                   "assign_pipeline,department,project_color,retake_workhour,date_length," \
                                   "ignore_weekend,last_submit_time,separated,frame,ingroup," \
                                   "cgworkhour,assigned_times,assigned_workhour,name,deadline," \
                                   "account_id " \
                                   "from timeaxis.task where cg_id similar to '({})'".format(cg_ids_connect)
                        try:
                            # print('开始解析任务组任务')
                            time_cursor.execute(task_sql)
                            task_result = time_cursor.fetchall()
                            if task_result:
                                # print('解析任务组001')
                                for t in task_result:
                                    data={}
                                    data['id'], data['task_type'], data['r_id'], data['artist'], data['position'], \
                                    data['start_date'],data['end_date'], data['status'], data['cg_id'], data['retake_info'], \
                                    data['task_name'],data['task_pipeline'], data['project_name'], data['artist_level'], data['difficulty_level'], \
                                    data['workhour'],data['module'], data['description'], data['assigned_time'], data['eps'],\
                                    data['img_path'],data['important_task_mark'], data['show_name'], data['assigner'], data['database'],\
                                    data['note'],data['project_shortname'], data['show_workhour'], data['client_status_retake_count'], data['sort_right'],\
                                    data['assign_pipeline'],data['department'], data['project_color'], data['retake_workhour'], data['date_length'],\
                                    data['ignore_weekend'],data['last_submit_time'], data['separated'], data['frame'], data['ingroup'],\
                                    data['cgworkhour'],data['assigned_times'], data['assigned_workhour'], data['name'], data['deadline'],\
                                    data['account_id'] = t
                                    data['deadline'] = data['deadline'] if data['deadline'] else ''
                                    if data['artist'] == gdata['artist']:
                                        task_data.update({data['cg_id']: data})
                        except Exception as group_task_ERR:
                            print('group_task_ERR:',str(group_task_ERR))

                    if gdata['artist'] not in group_tasks.keys():
                        group_tasks[gdata['artist']] = {gdata['id']:{'gdata':gdata,'task_data':task_data}}
                    else:
                        group_tasks[gdata['artist']].update({gdata['id']:{'gdata':gdata,'task_data':task_data}})

        except Exception as grouptask_in_departments_dates_ERR:
            print('grouptask_in_departments_dates_ERR:',str(grouptask_in_departments_dates_ERR))
        finally:
            time_connect.close()

        return group_tasks




    def task_in_dates(self,s_start_date,s_end_date,account_id=None):
        """
        提取指定时间段内的任务，获取有效的所有任务，包括cgtw任务与custom任务，排除掉show_workhour为空的任务(可以为0)。需要注意的有4点：
        1.所有show_workhour为空的任务都被过滤掉，没有统计进来。
        2.所有任务都以count_day作为日期跨日期长度。假如选择的日期是2020-12-10，但是一个任务的起始日期是2020-12-09,结束日期为2020-12-12，为4天
            那么该日期就被计算为从2020-12-10到2020-12-12。count_day就为3天。
        3.所有任务的工时以count_workhour作为计算。假如2中任务的show_workhour工时为12小时，那么count_workhour = (show_workrhour/4)*3
        4.所有任务在每天的工时都以均分的方式计算。参考3

        :param s_start_date: 选择的起始日期
        :param s_end_date:   选择的结束日期
        :return: 返回为每个任务的数据列表
        [{'task_type': 'cgtw', 'artist': '李静平', 'database': 'proj_spl', 'department': '05场景',
        'show_name': 'EP08_S32_C1030-env', 'show_workhour': '2', 'date_length': 1, 'start_date': '2020-11-21',
        'end_date': '2020-11-21', 'count_day': 1, 'count_workhour': 2.0},...}
        """
        all_task_info_list=[]

        select_cgtw = "select artist,database,status,department,show_name," \
                      "show_workhour,start_date,end_date,date_length,frame," \
                      "name,account_id,workhour FROM timeaxis.task where project_name is not null " \
                      "and project_name !='' and show_workhour is not null and show_workhour!='' and start_date <='{}' and end_date >='{}' " \
                      " ".format(s_end_date,s_start_date) #and cast(show_workhour as float)>0


        select_custom = "select custom_type,artist,database,department,show_name," \
                        "show_workhour,start_date,end_date,date_length,status," \
                        "account_id from timeaxis.custom_task where  " \
                        "show_workhour is not null and show_workhour!='' and  start_date <='{}' and end_date >='{}' " \
                        "".format(s_end_date,s_start_date)  #自定义任务 项目名称为空都算 非制作项目 #and cast(show_workhour as float)>0

        if account_id:
            print('有ID')
            select_cgtw = "select artist,database,status,department,show_name," \
                          "show_workhour,start_date,end_date,date_length,frame," \
                          "name,account_id,workhour from timeaxis.task where project_name is not null " \
                          "and project_name !='' and show_workhour is not null and show_workhour!='' and start_date <='{}' " \
                          "and end_date >='{}' and account_id='{}'" \
                          " ".format(s_end_date, s_start_date,account_id)  # and cast(show_workhour as float)>0

            select_custom = "select custom_type,artist,database,department,show_name," \
                            "show_workhour,start_date,end_date,date_length,status," \
                            "account_id from timeaxis.custom_task where  " \
                            "show_workhour is not null and show_workhour!='' and  start_date <='{}' and end_date >='{}' " \
                            "and account_id='{}'" \
                            "".format(s_end_date, s_start_date,account_id)

        time_connect, time_cursor = self._init_timeaxis_db()
        try:
            #获取cgtw 任务
            time_cursor.execute(select_cgtw)
            result = time_cursor.fetchall()
            if result:
                # print('cgtw任务有:',len(result))
                # print(result[0])
                over_task = []
                for i in result:
                    artist, database, status, department, show_name, \
                    show_workhour, start_date, end_date, date_length, frame, \
                    name,account_id,workhour = i
                    # date_length = 1
                    data = {}
                    data['task_type'] = 'cgtw'
                    data['artist'] = artist
                    data['database'] = database
                    data['status'] = status
                    data['department'] = department
                    data['show_name'] = show_name
                    data['show_workhour'] = show_workhour if show_workhour!='0' else workhour
                    data['date_length'] = date_length
                    data['start_date'] = start_date
                    data['end_date'] = end_date
                    data['frame'] = frame
                    data['account_id'] = account_id
                    data['count_day'] = date_length  # count_day用来计算切分日期后工时
                    data['cut_start_date'] = start_date  # 默认截断后的起始日期为 start_date
                    data['cut_end_date'] = end_date
                    s_deltel_day = self.mz_date.delta_date(s_start_date, start_date)  # 跨日期
                    e_deltel_day = self.mz_date.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day'] += s_deltel_day
                        over_task.append(data)
                        data['cut_start_date'] = s_start_date  #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        data['count_day'] += e_deltel_day
                        over_task.append(data)
                        data['cut_end_date'] = s_end_date  #

                    data['count_workhour']=(float(data['show_workhour'])/date_length) *data['count_day']  #被统计进来的工时

                    all_task_info_list.append(data)
                # print('cgtw over:task', len(over_task),over_task)

            #获取自定义任务
            time_cursor.execute(select_custom)
            custom_result = time_cursor.fetchall()
            if custom_result:
                # print('custom任务有:',len(custom_result))
                # print(custom_result[0])
                for c in custom_result:
                    custom_type,artist, database, department, show_name, \
                    show_workhour, start_date, end_date, date_length,status,\
                    account_id = c
                    c_data = {}
                    c_data['task_type'] = 'custom'
                    c_data['custom_type'] = custom_type
                    c_data['artist'] = artist
                    c_data['database'] = database if database and database != '' else 'noproject'
                    c_data['department'] = department
                    c_data['show_name'] = show_name
                    c_data['show_workhour'] = show_workhour
                    c_data['date_length'] = date_length
                    c_data['start_date'] = start_date
                    c_data['end_date'] = end_date
                    c_data['count_day'] = date_length  # count_day用来计算切分日期后工时
                    c_data['cut_start_date'] = start_date  # 默认截断后的起始日期为 start_date
                    c_data['cut_end_date'] = end_date
                    c_data['status'] = status
                    c_data['account_id'] = account_id

                    s_deltel_day = self.mz_date.delta_date(s_start_date, start_date)  # 跨日期
                    e_deltel_day = self.mz_date.delta_date(end_date, s_end_date)  # 跨日期
                    if s_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day'] += s_deltel_day
                        over_task.append(c_data)
                        data['cut_start_date'] = s_start_date  #
                    if e_deltel_day < 0:  # 当跨日期时，那么实际计算的日期跨度就减去
                        c_data['count_day'] += e_deltel_day
                        over_task.append(c_data)
                        c_data['cut_end_date'] = s_end_date  #
                    c_data['count_workhour'] = (float(show_workhour) / date_length) * c_data['count_day']  # 被统计进来的工时

                    all_task_info_list.append(c_data)
        except Exception as task_withworkhour_in_dates_ERR:
            print('task_withworkhour_in_dates_ERR:',str(task_withworkhour_in_dates_ERR))
        finally:
            time_connect.close()

        return all_task_info_list


    """
    ***********************************************
        projects表
        projects表记录排期软件中所有项目列表,本周优先,半边表栏的信息
        字段:
        id:
        row_index:     项目列表行索引
        column_index:  项目列表列索引
        project_name:  项目名称 eg: 涉过愤怒的海(FNDH)
        color:         项目颜色,排期软件中的项目颜色
        active:        项目是否激活到排期软件
        inweek:        项目是否在本周优先中显示
        week_row_index:     项目在本周优先栏的行索引
        week_column_index:  项目在本周优先栏的列索引
        inhalfyear:              项目是否在半年报栏中显示
        halfyear_row_index:      项目在半年报栏的行索引
        halfyear_column_index:   项目在半年报栏的列索引
        database:      项目对应的schema名称  eg:proj_xxss

        所需信息需根据具体要求来
    ***********************************************
    """

    def __projects_info(self):
        """
        排期软件项目中的项目信息
        :return: 返回已 database为键,data为值的字典
            {'proj_xxss':data,'proj_by':data}
        """

        time_connect, time_cursor = self._init_timeaxis_db()
        sql = "select id,row_index,column_index,project_name,color," \
              "active,inweek,week_row_index,week_column_index,inhalfyear," \
              "halfyear_row_index,halfyear_column_index,database from timeaxis.projects"
        info_map = {}
        try:
            time_cursor.execute(sql)
            result = time_cursor.fetchall()
            if result:
                for i in result:
                    pid, row_index, column_index, project_name, color, \
                    active, inweek, week_row_index, week_column_index, inhalfyear, \
                    halfyear_row_index, halfyear_column_index, database = i
                    data = {}
                    data['pid'] = pid
                    data['row_index'] = row_index
                    data['column_index'] = column_index
                    data['project_name'] = project_name
                    data['color'] = color
                    data['active'] = active
                    data['inweek'] = inweek
                    data['week_row_index'] = week_row_index
                    data['week_column_index'] = week_column_index
                    data['inhalfyear'] = inhalfyear
                    data['halfyear_row_index'] = halfyear_row_index
                    data['halfyear_column_index'] = halfyear_column_index
                    data['database'] = database
                    info_map[pid] = data
        except Exception as projects_info_ERR:
            print('projects_info_ERR:',str(projects_info_ERR))
        finally:
            time_connect.close()

        return info_map

    def get_active_projects_info(self):
        """
        获取projects表中的所有激活的姓名数据,这里是通过关联后得到的项目，而不是r_project表中的所有项目，这里的项目才会被显示
        :return:{'pid':{pid:xx,row_index:xx,...},'pid2':{...},...}
        """
        active_projects_info ={}
        if self.projects_info:
            for k ,v in self.projects_info.items():
                if v['active'] == 1:
                    active_projects_info[k] = v


        return active_projects_info



    """
    ***********************************************
        nodes表
        nodes表记录排期软件中所有添加的项目阶段信息
        字段:
        id:
        project_id: projects表中对应的id
        node_date:  节点日期
        node_info:  节点内容信息
        weight:     节点权重.当节点在同一天时,权重越大则越靠前
        update_time:节点添加\更新时间
        updater:    节点添加者

        所需信息需根据具体要求来
    ***********************************************
    """

    def _nodes_info(self):
        """
        获取nodes表中的所有节点数据,以projects中的id为键，获取该项目中的所有节点
        :return:
            nodes_info :{pid:[{id:xx,project_id:xx,...}],pid:{...}],...}
            nodes_info_datekey:{'2020-12-01':[{id:xx,project_id:xx,...}],'2020-12-03':{...}],...}
        """
        nodes_info={}

        time_connect, time_cursor=self._init_timeaxis_db()
        sql="select id,project_id,node_date,node_info,weight,update_time,updater from timeaxis.nodes "
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    nid, project_id, node_date, node_info, weight,update_time,updater=i
                    data={}
                    data['nid']=nid
                    data['project_id']=project_id
                    data['node_date']=node_date
                    data['node_info']=node_info
                    data['weight']=weight
                    data['update_time']=update_time
                    data['updater']=updater
                    if project_id not in nodes_info.keys():
                        nodes_info[project_id]=[data]
                    else:
                        nodes_info[project_id].append(data)

        except Exception as init_nodes_info_ERR:
            print('init_nodes_info_ERR:',str(init_nodes_info_ERR))
        finally:
            time_cursor.close()

        return nodes_info

    def sorted_node_info_by_date(self,nodes_info,_projects_info):
        """
        以日期为键的 节点信息,每次取之前计算一下,把项目名称与颜色拿进去,排除掉未激活的项目
        :param nodes_info:
        :param _projects_info:  {'pid':{pid:xx,row_index:xx,...},'pid2':{...},...}
        :return: nodes_info_datekey  #{'2020-12-01':[{id:xx,project_id:xx,...}],'2020-12-03':{...}],...}
        """
        nodes_info_datekey = {}  # 以日期为键的节点数据
        if nodes_info:
            for v in nodes_info.values():
                for data in v:
                    node_date=data['node_date']
                    pid=data['project_id']
                    pid_value=_projects_info.get(pid,None)   #projects_info永远只取projets表里active的项目
                    active =_projects_info[pid]['active'] if pid_value else 0   #node带有所有节点信息,所以排除掉
                    if active:  #仅读取激活的项目的nodes
                        data['color'] = _projects_info[pid]['color']
                        data['project_name']=_projects_info[pid]['project_name']
                        if node_date not in nodes_info_datekey.keys():
                            nodes_info_datekey[node_date] = [data]
                        else:
                            nodes_info_datekey[node_date].append(data)

        if nodes_info_datekey:  #在这里就根据weight进行排序，然后添加position坐标属性值，即Graph中的每人格子中的坐标
            for date,dl in nodes_info_datekey.items():
                s_list=sorted(dl,key=lambda x:x['weight'],reverse=True)  #根据weight排序,越大越靠前
                for i in range(len(s_list)):
                    s_list[i]['position'] = i   #按顺序添加position属性
                nodes_info_datekey[date] = s_list

        return nodes_info_datekey



    """
        ***********************************************
         数据读取
        ***********************************************
    """
    def get_table_colums(self,table_name,schema='timeaxis'):
        """
        获取指定表的所有列名
        :param table_name:
        :param schema:
        :return: list ['id','xxx']
        """

        sql="select column_name from information_schema.columns where table_name='{}' and table_schema='{}';".format(table_name,schema)
        time_connect, time_cursor = self._init_timeaxis_db()
        columns=[]
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()
            if result:
                for i in result:
                    columns.append(i[0])
        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))


        return columns


    def get_table_data(self,table_name,schema='timeaxis'):
        """
        获取指定表的所有数据,可用于pandas直接导出
        :param table_name:
        :return: [(),(),...]
        """
        result=None
        time_connect, time_cursor = self._init_timeaxis_db()

        sql="select * from {}.{} ".format(schema,table_name)
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()

        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))


        return result


    def get_table_data_by_column(self,table_name,column_names,schema='timeaxis'):
        """
        获取指定的
        :param table_name:
        :param column_names:
        :param schema:
        :return:
        """
        result=None
        time_connect, time_cursor = self._init_timeaxis_db()
        columns = ','.join(column_names)

        sql="select {} from {}.{} ".format(columns,schema,table_name)
        try:
            time_cursor.execute(sql)
            result=time_cursor.fetchall()

        except Exception as test_get_ori_data_ERR:
            print('test_get_ori_data_ERR:',str(test_get_ori_data_ERR))

        return result





    """
    ***********************************************
    ***********************************************

    测试

    ***********************************************
    ***********************************************
    """




